<?php
/*
 * 在原来的db.class.php基础上重写的数据库类,支持联表查询，链式查询
 * breeze @ 2015.12.4
 * 使用方法：
 * $db = new mdb(); 
 * $data = $db->queryAll('*', 'users');
 * $data = $db->queryRow('id, username', 'users', 'where id=1');
 * $data = $db->queryOne('username', 'users', 'where id=1');
 * $data = $db->select('b.name, a.*')->from('users', 'a')->leftjoin('user_group', 'b')->on('a.id=b.id')->query();
 * $data = $db->select('b.name, a.*')->from('users', 'a')->leftjoin('user_group', 'b')->on('a.id=b.id')->count();
 * $lastid = $db->insert('users', array('username' => 'test', 'uid'=>'94e7f57440b232d1c165e570e1de84bffb44c979'));
 * $db->update('users', array('username' => 'ttt'), array('id' => 18));
 * $db->delete('users', array('id' => 18));
 * echo '<pre>';
 * print_r($db->getLastSql());
 */
require_once dirname(__FILE__).'/../lib/adodb/adodb.inc.php';

class mdb {
    public $sql = '';
    public $ispdo = false;
    public $db = null;
    public $dbr = null;
    
    public function __construct() {
        $this->db = include(dirname(__FILE__).'/../conf/db.config.php');
        if ($this->db["pdo"] && class_exists('PDO')) $this->ispdo = true;
    }
    
    //主库操作
    public function dbconn() {
        if ($this->ispdo) {
            $conn = new PDO("mysql:host={$this->db['host']}:{$this->db['port']};dbname={$this->db['name']};charset=utf8",$this->db["user"],$this->db["pass"],array(PDO::ATTR_PERSISTENT=>true));
        } else {
            $conn = &ADONewConnection("mysql");
            @$conn->Connect($this->db["host"].":".$this->db["port"], $this->db["user"], $this->db["pass"], $this->db["name"]);
            $conn->SetFetchMode(ADODB_FETCH_ASSOC);
        }
        return $conn;
    }
    
    //从库操作
    public function dbconn_r() {
        if ($this->ispdo) {
            $conn = new PDO("mysql:host={$this->db['host_r']}:{$this->db['port']};dbname={$this->db['name']}",$this->db["user"],$this->db["pass"],array(PDO::ATTR_PERSISTENT=>true));
        } else {
            $conn = &ADONewConnection("mysql");
            @$conn->Connect($this->db["host_r"].":".$this->db["port"], $this->db["user"], $this->db["pass"], $this->db["name"]);
            $conn->SetFetchMode(ADODB_FETCH_ASSOC);
        }
        return $conn;
    }
    
    public function getLastSql(){
        return $this->sql;
    }
    
    public function db($mode = '') {
        if ($mode == 'r' && !empty($this->db["host_r"])) {
            $this->dbr = $this->dbconn_r();
        } else {
            $this->dbr = $this->dbconn();
        }
        return $this->dbr;
    }
    
    public function dblink($sql, $mode = '') {
        $this->sql = $sql;
        if ($this->ispdo) {
            if ($mode == 'r') {
                $ret = $this->db()->query($sql);
            } else {
                $ret = $this->db()->exec($sql);
            }
        } else {
            $this->db()->Execute("set names 'utf8'");
            $ret = $this->db()->Execute($sql);
        }
        return $ret;
    }
    
    public function queryAll($select, $table, $where="", $sort="", $limit="") {
        $rs = array();
        if (!empty($sort)) {
            $sort =" ORDER BY {$sort}";
        }
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($where) && count($where)>0) {
            foreach ($where as $k=>$v) {
                $whereinfo .= $k."=";
                if (is_numeric($v)) {
                    $whereinfo .= $v." AND ";
                } else {
                    $whereinfo .= "'".$v."' AND ";
                }
            }
            $whereinfo = rtrim($whereinfo," AND ");
        } else {
            $whereinfo = $where;
        }
        $sql = "SELECT {$select} FROM {$table} {$whereinfo} {$sort} {$limit}";
        $ret = $this->dblink($sql,'r');
        if ($this->ispdo) {
            $rs = $ret->fetchALL();
        } else {
            if (!empty($ret)) {
                while($row = $ret->FetchRow()){ 
                    array_push($rs, $row); 
                }
            }
        }
        return $rs;
    }
    
    public function queryRow($select = "*", $table, $where="") {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($where) && count($where)>0) {
            foreach ($where as $k=>$v) {
                $whereinfo .= $k."=";
                if (is_numeric($v)) {
                    $whereinfo .= $v." AND ";
                } else {
                    $whereinfo .= "'".$v."' AND ";
                }
            }
            $whereinfo = rtrim($whereinfo," AND ");
        } else {
            $whereinfo = $where;
        }
        $sql = "SELECT {$select} FROM {$table} {$whereinfo}";
        $ret = $this->dblink($sql, 'r');
        if (!empty($ret)) {
            if ($this->ispdo) {
                $rs = $ret->fetch();
            } else {
                $rs = $ret->FetchRow();
            }
        } else {
            $rs = "";
        }
        return $rs;
    }
    
    public function queryOne($select, $table, $where = "") {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($where) && count($where)>0){
            foreach ($where as $k=>$v) {
                $whereinfo .= $k."=";
                if (is_numeric($v)) {
                    $whereinfo .= $v." AND ";
                }else{
                    $whereinfo .= "'".$v."' AND ";
                }
            }
            $whereinfo = rtrim($whereinfo," AND ");
        }else{
            $whereinfo = $where;
        }
        $sql= "SELECT {$select} FROM {$table} {$whereinfo}";
        $ret = $this->dblink($sql,'r');
        if (!empty($ret)) {
            if ($this->ispdo) {
                $row = $ret->fetch();
            } else {
                $row = $ret->FetchRow();
            }
            if (is_array($row)) {
                $rs = $row[$select];
            } else {
                $rs = $row;
            }
        } else {
            $rs = "";
        }
        return $rs;
    }
     
    public function select($fields='*', $option='') {
        $this->sql = '';
        $this->sql .= "SELECT {$fields} ";
        return $this;
    }
    
    public function from($table, $alias = '') {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (!empty($alias)) {
            $this->sql .= "FROM {$table} AS {$alias} ";
        } else {
            $this->sql .= "FROM {$table} ";
        }
        return $this;
    }
    
    public function leftjoin($table, $alias = '') {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (!empty($alias)) {
            $this->sql .= "LEFT JOIN {$table} AS {$alias} ";
        } else {
            $this->sql .= "LEFT JOIN {$table} AS ";
        }
        return $this;
    }
    
    public function on($condition) {
        $this->sql .= "ON {$condition} ";
        return $this;
    }
    
    public function where($condition) {
        $this->sql .= "WHERE {$condition} ";
        return $this;
    }
    
    public function group($fields) {
        $this->sql .= "GROUP BY {$fields} ";
        return $this;
    }
    
    public function order($fields, $param = 'DESC') {
        $this->sql .= "ORDER BY {$fields} {$param} ";
        return $this;
    }
    
    public function limit($from = 0, $count) {
        $this->sql .= "LIMIT {$from}, {$count}";
        return $this;
    }
    
    public function query($mode = '') {
        $rs = array();
        $ret = $this->dblink($this->sql);
        if ($this->ispdo) {
            $rs = $ret->fetchALL();
        } else {
            if (!empty($ret)) {
                while ($row = $ret->FetchRow()) { 
                    array_push($rs, $row); 
                }
            }
        }
        return $rs;
    }
    
    public function count($mode = '') {
        $rs = array();
        $ret = $this->dblink($this->sql);
        if ($this->ispdo) {
            $record_nums = $ret->rowCount();
        } else {
            if (!empty($ret)) {
                $record_nums = $ret->RecordCount();
            }
            
        }
        return $record_nums;
    }
    
    public function insert($table, $into) {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($into) && count($into)>1) {
            foreach($into as $k=>$v) {
                $addinfo .= $k."=";
                if (is_numeric($v)) {
                    $addinfo .= $v.",";
                } else {
                    $addinfo .= "'".$v."',";
                }
            }
            $addinfo = trim($addinfo,",");
        }else{
            $addinfo = $into;
        }
        $sql = "INSERT INTO {$table} SET {$addinfo}";
        $this->$sql = $sql;
        $ret = $this->dblink($sql);
        //$newid = mysql_insert_id();
        if ($this->ispdo) {
            $newid = $this->db()->lastInsertId();
        } else {
            $newid = $this->db()->Insert_ID();
        }
        return $newid;
    }
    
    public function update($table, $vole, $where = "") {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($where) && count($where)>0) {
            $whereinfo = 'WHERE ';
            foreach ($where as $k=>$v) {
                $whereinfo .= $k."=";
                if (is_numeric($v)) {
                    $whereinfo .= $v." AND ";
                } else {
                    $whereinfo .= "'".$v."' AND ";
                }
            }
            $whereinfo = rtrim($whereinfo, " AND ");
        } else {
            $whereinfo = $where;
        }
        if (is_array($vole) && count($vole)>0){
            foreach($vole as $k=>$v){
                $intoinfo .= $k."=";
                if (is_numeric($v)) {
                    $intoinfo .= $v.",";
                } else {
                    $intoinfo .= "'".$v."',";
                }
            }
            $intoinfo = rtrim($intoinfo,",");
        } else {
            $intoinfo = $vole;
        }
        $sql = "UPDATE {$table} SET {$intoinfo} {$whereinfo}";
        $ret = $this->dblink($sql);
        return $ret;
    }    
    
    public function delete($table, $where) {
        if (!empty($this->db["dbbefore"])) $table = $this->db["dbbefore"].$table;
        if (is_array($where) && count($where)>0) {
            $whereinfo = 'WHERE ';
            foreach ($where as $k=>$v) {
                $whereinfo .= $k."=";
                if (is_numeric($v)) {
                    $whereinfo .= $v." AND ";
                } else {
                    $whereinfo .= "'".$v."' AND ";
                }
            }
            $whereinfo = rtrim($whereinfo, " AND ");
        } else {
            $whereinfo = $where;
        }
        $sql = "DELETE FROM {$table} {$whereinfo} ";
        $ret = $this->dblink($sql);
        return $ret;
    }
}